[GENERAL] Length of field names. - Mailing list pgsql-general

Dear All,

I think I'm having problems with PG because of the length of my fields
names.  I try:

brecard2=> create table malignant_pathologies_invasive (
rep_mal_invasive_carcinoma_rep_number        char(15) primary key,
rep_mal_invasive_carcinoma_pat_id            char(15),
rep_mal_invasive_carcinoma                   bool,
rep_mal_invasive_carcinoma_type_other text,
rep_mal_invasive_carcinoma_grade int2,
rep_mal_invasive_carcinoma_tubules  int2,
rep_mal_invasive_carcinoma_pleomorphism int2,
rep_mal_invasive_carcinoma_mitoses int2
);

ERROR:  CREATE TABLE/PRIMARY KEY multiple keys for table
malignant_pathologies_invasive are not legal

However, if I make the name of the table smaller as in:

brecard2=> create table mal_paths_invasive (...);

it works fine.  When I have a look at the table produce I get:

brecard2=> \d mal_paths_invasive

+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| rep_mal_invasive_carcinoma_rep_  | char() not null                  |    15 |
| rep_mal_invasive_carcinoma_pat_  | char()                           |    15 |
| rep_mal_invasive_carcinoma       | bool                             |     1 |
| rep_mal_invasive_carcinoma_type  | text                             |   var |
| rep_mal_invasive_carcinoma_grad  | int2                             |     2 |
| rep_mal_invasive_carcinoma_tubu  | int2                             |     2 |
| rep_mal_invasive_carcinoma_pleo  | int2                             |     2 |
| rep_mal_invasive_carcinoma_mito  | int2                             |     2 |
+----------------------------------+----------------------------------+-------+

So my guess is that with the original name, malignant_pathologies_invasive
and the primary key created by it (normally
malignant_pathologies_invasive_pkey) end up having the same name because of
the cut-off?

Does this seem right?  If so, what is the max length of field names and can
anyone suggest a way around this problem (obviously I could just give the
tables shorter names but they are computer generated by a Perl script and
if at all possible, I'd like to keep the filed and table names in this long
format).

Cheers,

S.

PS.  For those that have been following the \dt <regex> discussion, in this
case I would have like a '\d \dt' kind of query to get PG to give me the
type and length of the Field field (which is more of an SQL query type of
question so probably would have required SQL access to catalog tables as
Herouth suggested).






+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ University College London            |
| Tel. (0171) 878 4041    | 91 Riding House Street               |
| Fax. (0171) 878 4040    | London, W1P 8BT, UNITED KINGDOM.     |
+-------------------------+--------------------------------------+
| stuart@NOJUNK_ludwig.ucl.ac.uk [Remove NOJUNK_ for it to work] |
+----------------------------------------------------------------+